package com.oedtech.esl.repository.sys;

import com.oedtech.esl.model.sys.Store;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

@Repository
public interface StoreDao extends JpaRepository<Store,String>,JpaSpecificationExecutor<Store>{

    @Query(value = "select ap_id bs from sys_user u join sys_user_store us on u.id=us.user_id join store_bsid sb on us.store_code=sb.store_code where " +
            " u.username=?1",nativeQuery = true)
    List<String>findBsIDByUsername(String username);


    List<Store>findAllBySourceFlag(Integer sourceFlag);

    @Query(value = "select s.* from sys_user u join sys_user_store us on u.id=us.user_id join sys_store s on us.store_code=s.store_code where u.username=?1",nativeQuery = true)
    List<Store>findAllByUserName(String username);

    @Modifying
    @Transactional
    @Query(value = "update sys_store set password=?1,modify_time=now(),modify_who=?3 where store_code=?2",nativeQuery = true)
    int changePassword(String password, String storeCode,String modifyWho);


    @Query(value = "select s.channel_list from store_bsid sb join sys_store s on sb.store_code =s.store_code where sb.ap_id=?1",nativeQuery = true)
    String getChangeList(String bsid);

    @Modifying
    @Transactional
    @Query(value = "update sys_store set channel_list=?1 where store_code=(select store_code from store_bsid where ap_id =?2)",nativeQuery = true)
    int changeChannelList(String channelList, String bsid);


    boolean existsByStoreName(String storeName);

    @Modifying
    @Transactional
    @Query(value = "update sys_store set modify_time=now(),modify_who=?1 where store_code=?2",nativeQuery = true)
    int saveModify(String modifyWho,String storeCode);


    Store findByStoreCode(String storeCode);

    @Query(value = "select count(store_code) from sys_store ss JOIN vendor v on ss.source_flag =v.source_flag " +
            "where v.vendor_Code=?1 and ss.store_code =?2",nativeQuery = true)
    int existsByVendorCodeAndStoreCode(String vendorCode,String StoreCode);


    @Query(value = "select source_flag from sys_store GROUP by source_flag " +
            " HAVING source_flag=(select source_flag from sys_store where store_code=?1) and count(source_flag)<=1",nativeQuery = true)
    Integer findSourceFlagByStoreCode(String storeCode);


    @Query(value = "select sb.ap_id from sys_store ss join store_bsid sb on ss.store_code = sb.store_code join vendor v on ss.source_flag=v.source_flag" +
            "  where v.vendor_code=?1", nativeQuery = true)
    List<String> findApidByVendorCode(String vendorCode);



}
